﻿Imports System.Data
Imports System.Data.SqlClient
Public Class UsrCtrlBaoCao
    Public Sub LoadCBXMonHoc()
        Dim BaoCaoBus As BaoCaoBUS = New BaoCaoBUS
        Dim sql As String = "select * from MONHOC"
        BaoCaoBus.LoadCBX(cbxChonMonHoc, sql)
        cbxChonMonHoc.DisplayMember = "TenMonHoc"
        cbxChonMonHoc.ValueMember = "MaMonHoc"

        BaoCaoBus.LoadCBX(cbxMonHoc, sql)
        cbxMonHoc.DisplayMember = "TenMonHoc"
        cbxMonHoc.ValueMember = "MaMonHoc"
    End Sub
    Public Sub LoadCBXHocKy()
        Dim BaoCaoBus As BaoCaoBUS = New BaoCaoBUS
        Dim sql As String = "select distinct(TenHocKy) from HocKy"
        BaoCaoBus.LoadCBX(cbxChonHocKy, sql)
        cbxChonHocKy.DisplayMember = "TenHocKy"
        cbxChonHocKy.ValueMember = "TenHocKy"

        BaoCaoBus.LoadCBX(cbxHocKy, sql)
        cbxHocKy.DisplayMember = "TenHocKy"
        cbxHocKy.ValueMember = "TenHocKy"
    End Sub
    Public Sub LoadCBXLop()
        Dim BaoCaoBus As BaoCaoBUS = New BaoCaoBUS
        Dim sql As String = "select * from Lop"
        BaoCaoBus.LoadCBX(cbxChonLop, sql)
        cbxChonLop.DisplayMember = "TenLop"
        cbxChonLop.ValueMember = "MaLop"

        BaoCaoBus.LoadCBX(cbxLop, sql)
        cbxLop.DisplayMember = "TenLop"
        cbxLop.ValueMember = "MaLop"
    End Sub
    Public Sub LoadCBXNamHoc()
        Dim BaoCaoBus As BaoCaoBUS = New BaoCaoBUS
        Dim sql As String = "select distinct(NamHoc) from HocKy"
        BaoCaoBus.LoadCBX(cbxChonNamHoc, sql)
        cbxChonNamHoc.DisplayMember = "NamHoc"
        cbxChonNamHoc.ValueMember = "NamHoc"

        BaoCaoBus.LoadCBX(cbxNamHoc, sql)
        cbxNamHoc.DisplayMember = "NamHoc"
        cbxNamHoc.ValueMember = "NamHoc"
    End Sub
    Private Sub btnSearchBaoCao_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchBaoCao.Click
        Dim BaoCaoBus As BaoCaoBUS = New BaoCaoBUS
        Dim lop As String = cbxChonLop.SelectedValue.ToString()
        Dim mh As String = cbxChonMonHoc.SelectedValue.ToString()
        Dim hk As String = cbxChonHocKy.SelectedValue
        Dim nh As String = cbxChonNamHoc.SelectedValue
        Dim sql As String = "Select bc.MaBaoCao, bc.TenBaoCao, bc.SoLuongDat, bc.TyLe, l.TenLop, mh.TenMonHoc, hk.TenHocKy, hk.NamHoc " &
                            "from BaoCao bc, Lop l, MONHOC mh, HocKy hk " &
                            "where bc.MaLop = l.MaLop and bc.MaMonHoc = mh.MaMonHoc and bc.MaHocKy = hk.MaHocKy" &
                            " and bc.MaLop = '" & lop & "' and bc.MaMonHoc ='" & mh & "' and hk.TenHocKy = " & hk & " and hk.NamHoc = " & nh
        BaoCaoBus.LoadDGV(dgvBaoCao, sql)
    End Sub
    Public Sub LoadData()
        Dim BaoCaoBus As BaoCaoBUS = New BaoCaoBUS
        Dim sql As String = "Select bc.MaBaoCao, bc.TenBaoCao, bc.SoLuongDat, bc.TyLe, l.TenLop, mh.TenMonHoc, hk.TenHocKy, hk.NamHoc " &
                            "from BaoCao bc, Lop l, MONHOC mh, HocKy hk " &
                            "where bc.MaLop = l.MaLop and bc.MaMonHoc = mh.MaMonHoc and bc.MaHocKy = hk.MaHocKy"
        BaoCaoBus.LoadDGV(dgvBaoCao, sql)
    End Sub
    Private Sub btnThemBaoCao_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnThemBaoCao.Click
        Dim BaoCaoBus As BaoCaoBUS = New BaoCaoBUS
        Dim bc As BaoCaoDTO = New BaoCaoDTO
        bc.MaBaoCao = txtMaBaoCao.Text
        bc.TenBaoCao = txtTenBaoCao.Text
        bc.MaLop = cbxLop.SelectedValue.ToString()
        bc.MaMonHoc = cbxMonHoc.SelectedValue.ToString()
        Dim sl = Convert.ToInt32(txtSoLuongDat.Text)
        Dim GetDT As DataProvider = New DataProvider
        Dim sql As String = "select SiSo from Lop where MaLop ='" & bc.MaLop & "'"
        Dim reader As SqlDataReader = GetDT.SQL_ExcuteQueryReader(sql)
        If (reader.Read()) Then
            Dim siso As Integer = reader.GetInt32("0")
            If (sl > siso) Then
                MessageBox.Show("Số lượng đạt lớn hơn sĩ số của lớp, mời xem lại")
            Else
                bc.SoLuongDat = sl
                bc.TyLe = bc.SoLuongDat / siso
            End If
        End If
        Dim hk As Integer = cbxHocKy.SelectedValue
        Dim nh As Integer = cbxNamHoc.SelectedValue
        sql = "Select MaHocKy from HOCKY where TenHocKy = " & hk & " and NamHoc = " & nh
        reader = GetDT.SQL_ExcuteQueryReader(sql)
        If (reader.Read()) Then
            Dim mhk As String = reader.GetString("0")
            bc.MaHocKy = mhk
        End If
        If (BaoCaoBus.ThemBaoCao(bc)) Then
            MessageBox.Show("Thêm báo cáo thành công!", "^^", MessageBoxButtons.OK)
            LoadData()
        Else
            MessageBox.Show("Thêm thất bại, kiểm tra lại dữ liệu hoặc kết nối", "Opps!", MessageBoxButtons.OK)
        End If
    End Sub

    Private Sub btnXoaBaoCao_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnXoaBaoCao.Click
        Dim BaoCaoBus As BaoCaoBUS = New BaoCaoBUS
        Dim index As DataGridViewSelectedRowCollection = dgvBaoCao.SelectedRows
        For Each A As DataGridViewRow In index
            Dim MaBaoCao As String = A.Cells(0).Value.ToString()
            If (BaoCaoBus.XoaBaoCao(MaBaoCao) = False) Then
                MessageBox.Show("Xoá không thành công vui lòng kiểm tra lại kết nối mạng !!!", "Opps!", MessageBoxButtons.OK)
                Return
            End If
        Next
        MessageBox.Show("Xóa thành công !!!", "^^", MessageBoxButtons.OK)
        LoadData()
    End Sub

    Private Sub btnSuaBaoCao_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSuaBaoCao.Click
        Dim BaoCaoBus As BaoCaoBUS = New BaoCaoBUS
        Dim bc As BaoCaoDTO = New BaoCaoDTO
        bc.MaBaoCao = txtMaBaoCao.Text
        bc.TenBaoCao = txtTenBaoCao.Text
        bc.MaLop = cbxLop.SelectedValue.ToString()
        bc.MaMonHoc = cbxMonHoc.SelectedValue.ToString()
        Dim sl = Convert.ToInt32(txtSoLuongDat.Text)
        Dim GetDT As DataProvider = New DataProvider
        Dim sql As String = "select SiSo from Lop where MaLop ='" & bc.MaLop & "'"
        Dim reader As SqlDataReader = GetDT.SQL_ExcuteQueryReader(sql)
        If (reader.Read()) Then
            Dim siso As Integer = reader.GetInt32("0")
            If (sl > siso) Then
                MessageBox.Show("Số lượng đạt lớn hơn sĩ số của lớp, mời xem lại")
            Else
                bc.SoLuongDat = sl
                bc.TyLe = bc.SoLuongDat / siso
            End If
        End If
        Dim hk As Integer = cbxHocKy.SelectedValue
        Dim nh As Integer = cbxNamHoc.SelectedValue
        sql = "Select MaHocKy from HOCKY where TenHocKy = " & hk & " and NamHoc = " & nh
        reader = GetDT.SQL_ExcuteQueryReader(sql)
        If (reader.Read()) Then
            Dim mhk As String = reader.GetString("0")
            bc.MaHocKy = mhk
        End If
        If (BaoCaoBus.SuaBaoCao(bc)) Then
            MessageBox.Show("Cập nhật báo cáo thành công!", "^^", MessageBoxButtons.OK)
            LoadData()
        Else
            MessageBox.Show("cập nhật thất bại, kiểm tra lại dữ liệu hoặc kết nối", "Opps!", MessageBoxButtons.OK)
        End If
    End Sub

    Private Sub dgvBaoCao_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgvBaoCao.SelectionChanged
        txtMaBaoCao.Text = dgvBaoCao.CurrentRow.Cells(0).Value
        txtTenBaoCao.Text = dgvBaoCao.CurrentRow.Cells(1).Value
        txtSoLuongDat.Text = dgvBaoCao.CurrentRow.Cells(2).Value
        cbxLop.Text = dgvBaoCao.CurrentRow.Cells(4).Value
        cbxMonHoc.Text = dgvBaoCao.CurrentRow.Cells(5).Value
        cbxNamHoc.Text = dgvBaoCao.CurrentRow.Cells(6).Value
        cbxNamHoc.Text = dgvBaoCao.CurrentRow.Cells(7).Value.ToString()
    End Sub
End Class
